package com.pub.dbtools;

import org.jeecgframework.core.util.PropertiesUtil;

import java.util.ArrayList;
import java.util.HashMap;

public class DBFieldInfo {
	private static DBFieldInfo instance = null;
	private static HashMap fieldInfo = new HashMap();
	private static ArrayList allfieldname = new ArrayList();

	private DBFieldInfo() {
		allfieldname = new ArrayList();
		fieldInfo = new HashMap();
	}

	public static void MakeFieldInfo(String tablename) {
		allfieldname = new ArrayList();

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String currentdb = tmpvo.readProperty("jdbc.dbType");
		String currentuser = tmpvo.readProperty("jdbc.username");

		int nomsign = 0;
		if (currentdb.toLowerCase().equals("db2")) {
			String sqlStr = " select a.column_NAME,";
			sqlStr += " a.data_TYPE,a.CHARACTER_MAXIMUM_LENGTH,a.NUMERIC_SCALE,a.NUMERIC_PRECISION,a.IS_NULLABLE,a.column_key,a.column_COMMENT from columns a";
			sqlStr += " where trim(a.table_schema)='" + currentuser
					+ "' and trim(a.table_name)='"
					+ tablename.toUpperCase().trim() + "'";
			DataSimpDto tmpdto = new DataSimpDto("mysqltx", sqlStr);
			if (currentdb.toLowerCase().startsWith("mysql")) {
				for (int s = 0; s < tmpdto.getTotRecord(); s++) {
					String fieldname = "";

					nomsign = 0;
					if (fieldname.toLowerCase().equals("pub_fjbj")) {
						nomsign = 1;
					}
					if (fieldname.toLowerCase().equals("pub_jlzt")) {
						nomsign = 1;
					}
					if (fieldname.toLowerCase().equals("pub_userid")) {
						nomsign = 1;
					}
					if (fieldname.toLowerCase().equals("pub_deptid")) {
						nomsign = 1;
					}
					if (fieldname.toLowerCase().equals("pub_cjrqsj")) {
						nomsign = 1;
					}
					if (nomsign < 1)
						allfieldname.add(fieldname.toLowerCase().trim());
				}
			}
		}
		if (currentdb.toLowerCase().equals("db2")) {
			// db2
			String sqlStr = " select name,coltype,nulls,length,scale,keyseq,colno,REMARKS from sysibm.syscolumns ";
			sqlStr += " where UPPER(tbname)='" + tablename.toUpperCase().trim()
					+ "' and uppeR(rtrim(TBCREATOR))='" + currentuser + "'";
			DataSimpDto tmpdto = new DataSimpDto(sqlStr);

			for (int s = 0; s < tmpdto.getTotRecord(); s++) {
				String fieldname = tmpdto.getDataValue("name", s);
				nomsign = 0;
				if (fieldname.toLowerCase().equals("sys_fj_bj")) {
					nomsign = 1;
				}
				if (fieldname.toLowerCase().equals("sys_userid_bj")) {
					nomsign = 1;
				}
				if (fieldname.toLowerCase().equals("sys_dwnm_bj")) {
					nomsign = 1;
				}
				if (fieldname.toLowerCase().equals("sys_opersj_bj")) {
					nomsign = 1;
				}

				if (nomsign < 1)
					allfieldname.add(fieldname.toLowerCase().trim());
			}
		}
		if (currentdb.toLowerCase().equals("oracle")) {
			// oracle
			String sqlStr = "select a.CNAME as name,";
			sqlStr += "a.COLTYPE as coltype,a.WIDTH as length,a.SCALE as scale,a.PRECISION,a.NULLS as nulls,b.column_name keyseq,b.index_name,c.COMMENTS as remark from bscj.COLinfo a";
			sqlStr += " left join sys.all_ind_COLUMNS b on a.tname = b.table_name and a.cname=b.column_name";
			sqlStr += " left join SYS.ALL_COL_COMMENTS c on a.tname =c.TABLE_NAME and a.cname=c.COLUMN_NAME";
			sqlStr += " where uppeR(rtrim(a.tname)) ='"
					+ tablename.toUpperCase().trim()
					+ "' and uppeR(rtrim(c.owner))='"
					+ currentuser.toUpperCase().trim() + "'";

			DataSimpDto tmpdto = new DataSimpDto(sqlStr);

			for (int s = 0; s < tmpdto.getTotRecord(); s++) {
				String fieldname = tmpdto.getDataValue("name", s);
				nomsign = 0;
				if (fieldname.toLowerCase().equals("sys_fj_bj")) {
					nomsign = 1;
				}
				if (fieldname.toLowerCase().equals("sys_userid_bj")) {
					nomsign = 1;
				}
				if (fieldname.toLowerCase().equals("sys_dwnm_bj")) {
					nomsign = 1;
				}
				if (fieldname.toLowerCase().equals("sys_opersj_bj")) {
					nomsign = 1;
				}
				if (nomsign < 1)
					allfieldname.add(fieldname.toLowerCase().trim());
			}
		}
		fieldInfo.put(tablename.toLowerCase().trim(), allfieldname);
	}

	public static void MakeAllFieldInfo(String tablename) {
		allfieldname = new ArrayList();

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String currentdb = tmpvo.readProperty("jdbc.dbType");
		String currentuser = tmpvo.readProperty("jdbc.username");

		if (currentdb.toLowerCase().equals("db2")) {
			String sqlStr = " select a.column_NAME,";
			sqlStr += " a.data_TYPE,a.CHARACTER_MAXIMUM_LENGTH,a.NUMERIC_SCALE,a.NUMERIC_PRECISION,a.IS_NULLABLE,a.column_key,a.column_COMMENT from columns a";
			sqlStr += " where trim(a.table_schema)='" + currentuser
					+ "' and trim(a.table_name)='"
					+ tablename.toUpperCase().trim() + "'";
			DataSimpDto tmpdto = new DataSimpDto("mysqltx", sqlStr);
			if (currentdb.toLowerCase().startsWith("mysql")) {
				for (int s = 0; s < tmpdto.getTotRecord(); s++) {
					String fieldname = tmpdto.getDataValue("column_NAME", s);
					allfieldname.add(fieldname.toLowerCase().trim());
				}
			}
		}
		if (currentdb.toLowerCase().equals("db2")) {
			// db2
			String sqlStr = " select name,coltype,nulls,length,scale,keyseq,colno,REMARKS from sysibm.syscolumns ";
			sqlStr += " where UPPER(tbname)='" + tablename.toUpperCase().trim()
					+ "' and uppeR(rtrim(TBCREATOR))='" + currentuser + "'";
			DataSimpDto tmpdto = new DataSimpDto(sqlStr);

			for (int s = 0; s < tmpdto.getTotRecord(); s++) {
				String fieldname = tmpdto.getDataValue("name", s);
				String fieldtype = tmpdto.getDataValue("coltype", s);
				String ifpk = tmpdto.getDataValue("keyseq", s);
				String maxlength = tmpdto.getDataValue("length", s);
				String isnull = tmpdto.getDataValue("nulls", s);
				String scale = tmpdto.getDataValue("scale", s);
				String remark = tmpdto.getDataValue("remark", s);
				allfieldname.add(fieldname.toLowerCase().trim());
			}
		}
		if (currentdb.toLowerCase().equals("oracle")) {
			// oracle
			String sqlStr = "select a.CNAME as name,";
			sqlStr += "a.COLTYPE as coltype,a.WIDTH as length,a.SCALE as scale,a.PRECISION,a.NULLS as nulls,b.column_name keyseq,b.index_name,c.COMMENTS as remark from bscj.COLinfo a";
			sqlStr += " left join sys.all_ind_COLUMNS b on a.tname = b.table_name and a.cname=b.column_name";
			sqlStr += " left join SYS.ALL_COL_COMMENTS c on a.tname =c.TABLE_NAME and a.cname=c.COLUMN_NAME";
			sqlStr += " where uppeR(rtrim(a.tname)) ='"
					+ tablename.toUpperCase().trim()
					+ "' and uppeR(rtrim(c.owner))='"
					+ currentuser.toUpperCase().trim() + "'";

			DataSimpDto tmpdto = new DataSimpDto(sqlStr);

			for (int s = 0; s < tmpdto.getTotRecord(); s++) {
				String fieldname = tmpdto.getDataValue("name", s);
				String fieldtype = tmpdto.getDataValue("coltype", s);
				String ifpk = tmpdto.getDataValue("keyseq", s);
				String maxlength = tmpdto.getDataValue("length", s);
				String isnull = tmpdto.getDataValue("nulls", s);
				String scale = tmpdto.getDataValue("scale", s);
				String remark = tmpdto.getDataValue("remark", s);
				String iname = tmpdto.getDataValue("index_name", s);
				allfieldname.add(fieldname.toLowerCase().trim());
			}
		}
		fieldInfo.put(tablename.toLowerCase().trim(), allfieldname);
	}

	public static void refresh() {
		allfieldname = new ArrayList();
		fieldInfo = new HashMap();
	}

	public void toTableInfo(String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfo.get(tablename.toLowerCase()) == null) {
			MakeFieldInfo(tablename.toLowerCase());
		}
	}

	public void toTableInfoAll(String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfo.get(tablename.toLowerCase()) == null) {
			MakeAllFieldInfo(tablename.toLowerCase());
		}
	}

	public ArrayList getAllFieldName(String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfo.get(tablename) == null) {
			MakeFieldInfo(tablename);
		}
		return (ArrayList) fieldInfo.get(tablename.toLowerCase());
	}

	public ArrayList getWFieldName(String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfo.get(tablename) == null) {
			MakeAllFieldInfo(tablename);
		}
		return (ArrayList) fieldInfo.get(tablename.toLowerCase());
	}

	public synchronized static DBFieldInfo getInst() {
		if (instance == null) {
			instance = new DBFieldInfo();
		}
		return instance;
	}
}
